﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// 文件名：ClsStockInvoiceDAL.cs
/// 作  者：Kidy
/// 时  间：2006.12.4
/// 说  明：ClsStockInvoiceDAL类用于采购发票信息相关处理
/// </summary>
public class ClsStockInvoiceDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取所有主表信息
    /// </summary>
    /// <param name="Type">类型</param>
    /// <returns>返回主表信息数据集</returns>
    public DataSet GetStockInvoiceMains(string sType, string sCurUserId)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStockInvoiceMains", con);
        dat.SelectCommand.CommandType = CommandType.StoredProcedure;

        dat.SelectCommand.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        dat.SelectCommand.Parameters["@type"].Value = sType;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = sCurUserId;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取当前采购发票从表信息
    /// <param name="sVm_no"></param>
    /// <param name="Type">类型</param>
    /// </summary>CREATE BY KIDY ON 20070106
    /// <returns>返回从表信息数据集</returns>
    public DataSet GetStockInvoiceChilds(string sVm_No, string Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStockInvoiceChilds", con);
        dat.SelectCommand.CommandType = CommandType.StoredProcedure;

        dat.SelectCommand.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@vm_no"].Value = sVm_No;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 修改采购发票
    /// </summary>CREATE BY KIDY ON 20070107  
    /// <returns>操作是否成功</returns>
    /// strType, ctbVm_no.Text.Trim(), DateTime.Parse(ctsbRsMakeOutDate.Text.Trim()),ctbRsRemark.Text.Trim()
    public Boolean UpdateStockInvoice(string sType,string sVm_no,DateTime dMakeOutDate,string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateStockInvoiceM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@vm_no"].Value = sVm_no;
        com.Parameters.Add("@vm_date", SqlDbType.DateTime, 8);
        com.Parameters["@vm_date"].Value = dMakeOutDate;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;        

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 改变订单从表的状态
    /// </summary>
    /// <param name="sVm_no">订单单号</param>
    /// <param name="N_No">从表单号</param>
    /// <param name="State">操作状态(0确认，1取消确认即终止，2删除即作废，3彻底删除)</param>
    /// <param name="Type">类型</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdateStockInvoiceChildState(string sVm_no, string N_No, int State, string Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateStockInvoiceChildState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@sVm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@sVm_no"].Value = sVm_no;
        com.Parameters.Add("@n_no", SqlDbType.NVarChar, 30);
        com.Parameters["@n_no"].Value = N_No;
        com.Parameters.Add("@dm_state", SqlDbType.Int, 4);
        com.Parameters["@dm_state"].Value = State;
        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

  /// <summary>
  /// 新增采购发票记录
  /// </summary>CREATE BY KIDY ON 20070105  
  /// <returns></returns>
  /// strVm_no, dVm_Date, strInvoiceNo, strStoValiNo, strVali_date,
  ///strPro_id, strDept_id, strWorker_id, strAddr, strPhone, strBk_id, strTaxNo, strBacc_no,strRemark
    public Boolean AddStockInvoice(string sType,string sVm_no, DateTime dVm_Date, string sInvoiceNo, string sStoValiNo, DateTime dVali_date,
                                    string sPro_id, string sDept_id, string sWorker_id, string sAddr, string sPhone,
                                    string sBk_id, string sTaxNo, string sBacc_no,string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddStockInvoice", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@Vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Vm_no"].Value = sVm_no;
        com.Parameters.Add("@Vm_Date", SqlDbType.DateTime, 8);
        com.Parameters["@Vm_Date"].Value = dVm_Date;
        com.Parameters.Add("@InvoiceNo", SqlDbType.NVarChar, 30);
        com.Parameters["@InvoiceNo"].Value = sInvoiceNo;
        com.Parameters.Add("@StoValiNo", SqlDbType.NVarChar, 30);
        com.Parameters["@StoValiNo"].Value = sStoValiNo;
        com.Parameters.Add("@Vali_date", SqlDbType.DateTime, 8);
        com.Parameters["@Vali_date"].Value = dVali_date;
        com.Parameters.Add("@Pro_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Pro_id"].Value = sPro_id;
        com.Parameters.Add("@Dept_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Dept_id"].Value = sDept_id;
        com.Parameters.Add("@Worker_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Worker_id"].Value = sWorker_id;
        com.Parameters.Add("@Addr", SqlDbType.NVarChar, 60);
        com.Parameters["@Addr"].Value = sAddr;
        com.Parameters.Add("@Phone", SqlDbType.NVarChar, 30);
        com.Parameters["@Phone"].Value = sPhone;
        com.Parameters.Add("@Bk_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Bk_id"].Value = sBk_id;
        com.Parameters.Add("@TaxNo", SqlDbType.NVarChar, 30);
        com.Parameters["@TaxNo"].Value = sTaxNo;
        com.Parameters.Add("@Bacc_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Bacc_no"].Value = sBacc_no;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改从表记录
    /// </summary>
    public Boolean UpdateStockInvoiceChild(string sType, string sVm_No, string sIm_No, string sBatch_No, decimal iQty, 
        decimal dPrice,decimal dAgio,decimal dTaxMoney, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateStockInvoiceChild", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@vm_no"].Value = sVm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = sIm_No;
        com.Parameters.Add("@batch_no", SqlDbType.NVarChar, 30);
        com.Parameters["@batch_no"].Value = sBatch_No;
        com.Parameters.Add("@qty", SqlDbType.Decimal,28);
        com.Parameters["@qty"].Value = iQty;
        com.Parameters.Add("@price", SqlDbType.Decimal, 28);
        com.Parameters["@price"].Value = dPrice;
        com.Parameters.Add("@agio", SqlDbType.Decimal, 28);
        com.Parameters["@agio"].Value = dAgio;
        com.Parameters.Add("@taxMoney", SqlDbType.Decimal, 28);
        com.Parameters["@taxMoney"].Value = dTaxMoney;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    public DataSet GetStockMains(string aa, string bb, string cc)
    {
        return null;
    }

    public DataSet GetStockChilds(string sVm_no, string Type)
    {
        return null;
    }

    /// <summary>
    /// 删除采购发票主表或者明细表记录 
    /// </summary>
    /// <param>CREATE BY KIDY ON 20070106</param>
    /// <param name="Is_Visible"></param>
    /// <returns>返回删除是否成功</returns>
    public Boolean DeleteStockInvoice(string sType, string sVm_no, string sIm_no, string sLot_no, int iState)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteStockInvoice", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 30);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@Vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Vm_no"].Value = sVm_no;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@state", SqlDbType.Int, 1);
        com.Parameters["@state"].Value = iState;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 改变订单的状态
    /// </summary>CREATE BY KIDY ON 20070107
    /// <returns>操作是否成功</returns>
    public Boolean AuditStockInvoiceState(string sType,string sVm_No,string sInto_no,string sAuditor,int iState)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditBuyInvoice", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@vm_no"].Value = sVm_No;
        com.Parameters.Add("@Into_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Into_no"].Value = sInto_no;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 30);
        com.Parameters["@auditor"].Value = sAuditor;
        com.Parameters.Add("@state", SqlDbType.Bit);
        com.Parameters["@state"].Value = iState;
        
        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 检查采购发票明细中商品数量是否可以正确修改
    /// </summary>CREATE BY KIDY ON 20070118
    /// <param name="sType"></param>
    /// <param name="sInto_no"></param>
    /// <returns></returns>
    public int ChkIsUpdate(string sType, string sInVali_no,string sIm_no,string sLot_no)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_ChkIsUpdateByStockInvoice", con);//Pro_ChkIsAuditByBuyInvoice
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter ParOutPut = new SqlParameter();

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@InVali_no", SqlDbType.NVarChar, 30);
        com.Parameters["@InVali_no"].Value = sInVali_no;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = sIm_no;
        //com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        //com.Parameters["@lot_no"].Value = sLot_no;
        //ParOutPut = com.Parameters.Add("@reIm_no", SqlDbType.NVarChar,30);
        //ParOutPut.Direction = ParameterDirection.Output;
        //ParOutPut = com.Parameters.Add("@reLot_no", SqlDbType.NVarChar,30);
        //ParOutPut.Direction = ParameterDirection.Output;
        //ParOutPut = com.Parameters.Add("@reState", SqlDbType.Int);
        //ParOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            int iSpare_qty = int.Parse(com.ExecuteScalar().ToString());
            return iSpare_qty;
            //string strReState = com.Parameters["@reState"].Value.ToString();//返回状态（0:成功;1:失败）
            //string strIm_no = com.Parameters["@reIm_no"].Value.ToString();//返回商品ID
            //string strLot_no = com.Parameters["@reLot_no"].Value.ToString();//返回商品批号
            //if (strReState == "0")
            //{
            //    return strReState;
            //}
            //else
            //{
            //    return strIm_no + "|" + strLot_no;
            //}
        }
        catch
        {
            return 0;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

    }

    /// <summary>
    /// 根据查询状态得到采购发票单据信息
    /// </summary>CREATE BY KIDY ON 20070107
    /// <param name="Type">类型</param>
    /// <returns>返回主表信息数据集</returns>
    public DataSet GetOrderInformationByStatu(string sCurUserId,string sTable, int iType, int iQueryType)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetOrderByStockInvoice", con);
        dat.SelectCommand.CommandType = CommandType.StoredProcedure;

        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.VarChar, 30);
        dat.SelectCommand.Parameters["@u_log_id"].Value = sCurUserId;
        dat.SelectCommand.Parameters.Add("@table", SqlDbType.VarChar, 30);
        dat.SelectCommand.Parameters["@table"].Value = sTable;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 2);
        dat.SelectCommand.Parameters["@type"].Value = iType;
        dat.SelectCommand.Parameters.Add("@queryType", SqlDbType.Int, 2);
        dat.SelectCommand.Parameters["@queryType"].Value = iQueryType;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

}
